﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
using System.Linq;
using System.Text;
using System.IO;
using System.Threading.Tasks;

namespace Logistics.DataAccess
{
    public class TC_OWR_FILE
    {
        public bool Add(Model.TC_OWR_FILE model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO TC_OWR_FILE(");
            strSql.Append("TC_OWR001,");
            strSql.Append("TC_OWR002,");
            strSql.Append("TC_OWR003,");
            strSql.Append("TC_OWR004,");
            strSql.Append("TC_OWR005,");
            strSql.Append("TC_OWR006,");
            strSql.Append("TC_OWR007,");
            strSql.Append("TC_OWR008,");
            strSql.Append("TC_OWR009,");
            strSql.Append("TC_OWR010,");
            strSql.Append("TC_OWR0101,");
            strSql.Append("TC_OWR011,");
            strSql.Append("TC_OWR012,");
            strSql.Append("TC_OWR013,");
            strSql.Append("TC_OWR014,");
            strSql.Append("TC_OWR015,");
            strSql.Append("TC_OWR016,");
            strSql.Append("TC_OWR017,");
            strSql.Append("TC_OWR018,");
            strSql.Append("TC_OWR019,");
            strSql.Append("TC_OWR020,");
            strSql.Append("TC_OWR021,");
            strSql.Append("TC_OWR0131,");
            strSql.Append("TC_OWR022,");
            strSql.Append("TC_OWR0191,");
            strSql.Append("TC_OWR0132,");
            strSql.Append("TC_OWR0133,");
            strSql.Append("TC_OWR023,");
            strSql.Append("TC_OWR024,");
            strSql.Append("TC_OWR025,");
            strSql.Append("TC_OWR026,");
            strSql.Append("TC_OWR027)");
            strSql.Append(" VALUES (");
            strSql.Append("@in_TC_OWR001,");
            strSql.Append("@in_TC_OWR002,");
            strSql.Append("@in_TC_OWR003,");
            strSql.Append("@in_TC_OWR004,");
            strSql.Append("@in_TC_OWR005,");
            strSql.Append("@in_TC_OWR006,");
            strSql.Append("@in_TC_OWR007,");
            strSql.Append("@in_TC_OWR008,");
            strSql.Append("@in_TC_OWR009,");
            strSql.Append("@in_TC_OWR010,");
            strSql.Append("@in_TC_OWR0101,");
            strSql.Append("@in_TC_OWR011,");
            strSql.Append("@in_TC_OWR012,");
            strSql.Append("@in_TC_OWR013,");
            strSql.Append("@in_TC_OWR014,");
            strSql.Append("@in_TC_OWR015,");
            strSql.Append("@in_TC_OWR016,");
            strSql.Append("@in_TC_OWR017,");
            strSql.Append("@in_TC_OWR018,");
            strSql.Append("@in_TC_OWR019,");
            strSql.Append("@in_TC_OWR020,");
            strSql.Append("@in_TC_OWR021,");
            strSql.Append("@in_TC_OWR0131,");
            strSql.Append("@in_TC_OWR022,");
            strSql.Append("@in_TC_OWR0191,");
            strSql.Append("@in_TC_OWR0132,");
            strSql.Append("@in_TC_OWR0133,");
            strSql.Append("@in_TC_OWR023,");
            strSql.Append("@in_TC_OWR024,");
            strSql.Append("@in_TC_OWR025,");
            strSql.Append("@in_TC_OWR026,");
            strSql.Append("@in_TC_OWR027)");
            OracleParameter[] cmdParms = {
                DBHelper.CreateDbParameter("@in_TC_OWR001", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR001)),
                DBHelper.CreateDbParameter("@in_TC_OWR002", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR002)),
                DBHelper.CreateDbParameter("@in_TC_OWR003", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR003)),
                DBHelper.CreateDbParameter("@in_TC_OWR004", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR004)),
                DBHelper.CreateDbParameter("@in_TC_OWR005", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR005)),
                DBHelper.CreateDbParameter("@in_TC_OWR006", DbType.Date,7 ,DBValue.GetDBValue(model.TC_OWR006)),
                DBHelper.CreateDbParameter("@in_TC_OWR007", DbType.Date,7 ,DBValue.GetDBValue(model.TC_OWR007)),
                DBHelper.CreateDbParameter("@in_TC_OWR008", DbType.Date,7 ,DBValue.GetDBValue(model.TC_OWR008)),
                DBHelper.CreateDbParameter("@in_TC_OWR009", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR009)),
                DBHelper.CreateDbParameter("@in_TC_OWR010", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR010)),
                DBHelper.CreateDbParameter("@in_TC_OWR0101", DbType.String,35 ,DBValue.GetDBValue(model.TC_OWR0101)),
                DBHelper.CreateDbParameter("@in_TC_OWR011", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR011)),
                DBHelper.CreateDbParameter("@in_TC_OWR012", DbType.String,5 ,DBValue.GetDBValue(model.TC_OWR012)),
                DBHelper.CreateDbParameter("@in_TC_OWR013", DbType.String,50 ,DBValue.GetDBValue(model.TC_OWR013)),
                DBHelper.CreateDbParameter("@in_TC_OWR014", DbType.String,12 ,DBValue.GetDBValue(model.TC_OWR014)),
                DBHelper.CreateDbParameter("@in_TC_OWR015", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR015)),
                DBHelper.CreateDbParameter("@in_TC_OWR016", DbType.String,200 ,DBValue.GetDBValue(model.TC_OWR016)),
                DBHelper.CreateDbParameter("@in_TC_OWR017", DbType.String,200 ,DBValue.GetDBValue(model.TC_OWR017)),
                DBHelper.CreateDbParameter("@in_TC_OWR018", DbType.String,200 ,DBValue.GetDBValue(model.TC_OWR018)),
                DBHelper.CreateDbParameter("@in_TC_OWR019", DbType.String,1 ,DBValue.GetDBValue(model.TC_OWR019)),
                DBHelper.CreateDbParameter("@in_TC_OWR020", DbType.Date,7 ,DBValue.GetDBValue(model.TC_OWR020)),
                DBHelper.CreateDbParameter("@in_TC_OWR021", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR021)),
                DBHelper.CreateDbParameter("@in_TC_OWR0131", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR0131)),
                DBHelper.CreateDbParameter("@in_TC_OWR022", DbType.String,1 ,DBValue.GetDBValue(model.TC_OWR022)),
                DBHelper.CreateDbParameter("@in_TC_OWR0191", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR0191)),
                DBHelper.CreateDbParameter("@in_TC_OWR0132", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR0132)),
                DBHelper.CreateDbParameter("@in_TC_OWR0133", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR0133)),
                DBHelper.CreateDbParameter("@in_TC_OWR023", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR023)),
                DBHelper.CreateDbParameter("@in_TC_OWR024", DbType.Date,7 ,DBValue.GetDBValue(model.TC_OWR024)),
                DBHelper.CreateDbParameter("@in_TC_OWR025", DbType.String,1 ,DBValue.GetDBValue(model.TC_OWR025)),
                DBHelper.CreateDbParameter("@in_TC_OWR026", DbType.String,10 ,DBValue.GetDBValue(model.TC_OWR026)),
                DBHelper.CreateDbParameter("@in_TC_OWR027", DbType.String,1 ,DBValue.GetDBValue(model.TC_OWR027)),
            };

            return DBHelper.ExecuteSql(strSql.ToString(), cmdParms) > 0;
        }

        public List<Model.TC_OWR_FILE> GetList(List<string> owi, string l_date, string status,string l_type)
        {
            StringBuilder strSql = new StringBuilder("SELECT * FROM TC_OWR_FILE WHERE 1=1");
            if (owi.Count == 1)
                strSql.AppendFormat(" AND TC_OWR009 = '{0}'", string.Join(",", owi));
            if (owi.Count > 1)
                strSql.AppendFormat(" AND TC_OWR009 IN ({0})", string.Join(",", owi));
            if (!string.IsNullOrEmpty(l_date))
                strSql.AppendFormat(" AND TC_OWR006 = to_date('{0}','yyyy-mm-dd')", l_date);
            if (!string.IsNullOrEmpty(status))
                strSql.AppendFormat(" AND TC_OWR019='{0}'", status);
            if (!string.IsNullOrEmpty(l_type))
                strSql.AppendFormat(" AND TC_OWR001='{0}'", l_type);
            using (DbDataReader dr = DBHelper.ExecuteReader(strSql.ToString()))
            {
                List<Model.TC_OWR_FILE> lst = GetList(dr);
                return lst;
            }
        }
        public string Del(Model.TC_OWR_FILE model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT tc_owr019 FROM TC_OWR_FILE WHERE tc_owr001 = '{0}' AND tc_owr002 = '{1}'", model.TC_OWR001, model.TC_OWR002);
            object l_tc_owr019 = DBHelper.GetSingle(strSql.ToString());

            if (string.IsNullOrEmpty(Convert.ToString(l_tc_owr019)))
            {
                return "0";
            }
            else
            {
                if (l_tc_owr019.ToString() == "1" || l_tc_owr019.ToString() == "D")
                {
                    strSql.Clear();
                    strSql.AppendFormat("DELETE FROM TC_OWR_FILE WHERE tc_owr001 = '{0}' AND tc_owr002 = '{1}'", model.TC_OWR001, model.TC_OWR002);
                    DBHelper.ExecuteSql(strSql.ToString());
                    strSql.Clear();
                    strSql.AppendFormat("DELETE FROM TC_OWS_FILE WHERE tc_ows000 = '{0}' AND tc_ows001 = '{1}'", model.TC_OWR001, model.TC_OWR002);
                    DBHelper.ExecuteSql(strSql.ToString());
                    strSql.Clear();
                    strSql.AppendFormat("DELETE FROM TC_OWW_FILE WHERE tc_oww000 = '{0}' AND tc_oww001 = '{1}'", model.TC_OWR001, model.TC_OWR002);
                    DBHelper.ExecuteSql(strSql.ToString());
                    return "0";
                }
                else
                {
                    return l_tc_owr019.ToString();
                }
            }

        }


        /// <summary>
        /// 导出状态还原
        /// </summary>
        public bool ExportStatus(string l_tc_owr001, string l_tc_owr002)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE edi_test.tc_owr_file SET tc_owr019 = '1' ");
            strSql.Append($"Where tc_owr001 ={l_tc_owr001} ");
            strSql.Append($"and tc_owr002 ={l_tc_owr002} ");
            strSql.Append("AND tc_owr019 = '2'  ");
            DBHelper.ExecuteSql(strSql.ToString());
            return true;
        }

        /// <summary>
        /// 导出物流状态切换
        /// </summary>
        public bool ChangeWuLiuStatus(string l_tc_owr001, string l_tc_owr002, string l_tc_owr019)
        {
            var dec_tc_owr019 = "1";
            if (l_tc_owr019 == "0" || l_tc_owr019 == "1")
                dec_tc_owr019 = "5";

            StringBuilder strSql = new StringBuilder();
            strSql.Append($"UPDATE edi_test.tc_owr_file SET tc_owr019 ='{dec_tc_owr019}' ");
            strSql.Append($"Where tc_owr001 ={l_tc_owr001} ");
            strSql.Append($"and tc_owr002 ={l_tc_owr002} ");
            strSql.Append($"AND tc_owr019 in({l_tc_owr019})");
            DBHelper.ExecuteSql(strSql.ToString());
            return true;
        }


        public bool OwsReturn(string l_tc_ows000, string l_tc_ows001, string l_tc_ows002,string l_tc_ows013, string l_tc_ows014)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append($"UPDATE edi_test.tc_ows_file SET tc_ows013 ='{l_tc_ows013}',tc_ows014='{l_tc_ows014}' ");
            strSql.Append($"Where tc_ows000 ={l_tc_ows000} ");
            strSql.Append($"and tc_ows001 ={l_tc_ows001} ");
            strSql.Append($"and tc_ows002 ={l_tc_ows002} ");
            DBHelper.ExecuteSql(strSql.ToString());
            return true;
        }

        public bool OwrReturn(string l_tc_ows000, string l_tc_ows001)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append($"UPDATE edi_test.tc_owr_file SET tc_owr019 ='3' ");
            strSql.Append($"Where tc_owr001 ={l_tc_ows000} ");
            strSql.Append($"and tc_owr002 ={l_tc_ows001} ");
            strSql.Append($"and tc_owr019 ='2' ");
            DBHelper.ExecuteSql(strSql.ToString());
            return true;
        }

        #region 
        /// <summary>
        /// 由一行数据得到一个实体
        /// </summary>
        private Model.TC_OWR_FILE GetModel(DbDataReader dr)
        {
            Model.TC_OWR_FILE model = new Model.TC_OWR_FILE();
            model.TC_OWR001 = DBHelper.GetString(dr["TC_OWR001"]);
            model.TC_OWR002 = DBHelper.GetString(dr["TC_OWR002"]);
            model.TC_OWR003 = DBHelper.GetString(dr["TC_OWR003"]);
            model.TC_OWR004 = DBHelper.GetString(dr["TC_OWR004"]);
            model.TC_OWR005 = DBHelper.GetString(dr["TC_OWR005"]);
            model.TC_OWR006 = DBHelper.GetDateTime(dr["TC_OWR006"]);
            model.TC_OWR007 = DBHelper.GetDateTime(dr["TC_OWR007"]);
            model.TC_OWR008 = DBHelper.GetDateTime(dr["TC_OWR008"]);
            model.TC_OWR009 = DBHelper.GetString(dr["TC_OWR009"]);
            model.TC_OWR010 = DBHelper.GetString(dr["TC_OWR010"]);
            model.TC_OWR0101 = DBHelper.GetString(dr["TC_OWR0101"]);
            model.TC_OWR011 = DBHelper.GetString(dr["TC_OWR011"]);
            model.TC_OWR012 = DBHelper.GetString(dr["TC_OWR012"]);
            model.TC_OWR013 = DBHelper.GetString(dr["TC_OWR013"]);
            model.TC_OWR014 = DBHelper.GetString(dr["TC_OWR014"]);
            model.TC_OWR015 = DBHelper.GetString(dr["TC_OWR015"]);
            model.TC_OWR016 = DBHelper.GetString(dr["TC_OWR016"]);
            model.TC_OWR017 = DBHelper.GetString(dr["TC_OWR017"]);
            model.TC_OWR018 = DBHelper.GetString(dr["TC_OWR018"]);
            model.TC_OWR019 = DBHelper.GetString(dr["TC_OWR019"]);
            model.TC_OWR020 = DBHelper.GetDateTime(dr["TC_OWR020"]);
            model.TC_OWR021 = DBHelper.GetString(dr["TC_OWR021"]);
            model.TC_OWR0131 = DBHelper.GetString(dr["TC_OWR0131"]);
            model.TC_OWR022 = DBHelper.GetString(dr["TC_OWR022"]);
            model.TC_OWR0191 = DBHelper.GetString(dr["TC_OWR0191"]);
            model.TC_OWR0132 = DBHelper.GetString(dr["TC_OWR0132"]);
            model.TC_OWR0133 = DBHelper.GetString(dr["TC_OWR0133"]);
            model.TC_OWR023 = DBHelper.GetString(dr["TC_OWR023"]);
            model.TC_OWR024 = DBHelper.GetDateTime(dr["TC_OWR024"]);
            model.TC_OWR025 = DBHelper.GetString(dr["TC_OWR025"]);
            model.TC_OWR026 = DBHelper.GetString(dr["TC_OWR026"]);
            model.TC_OWR027 = DBHelper.GetString(dr["TC_OWR027"]);
            return model;
        }

        /// <summary>
        /// 由DbDataReader得到泛型数据列表
        /// </summary>
        private List<Model.TC_OWR_FILE> GetList(DbDataReader dr)
        {
            List<Model.TC_OWR_FILE> lst = new List<Model.TC_OWR_FILE>();
            while (dr.Read())
            {
                lst.Add(GetModel(dr));
            }
            return lst;
        }
        #endregion
    }
}
